2020年3月にリニューアルしたDevelopers.IOの変更点をインフラ側から紹介します

2020年3月にリニューアルしたDevelopers.IOの変更点をインフラ側から紹介します

2020年3月にリニューアルしたブログインフラの改善点について紹介します。
#WordPress
#ブログ
#Amazon Aurora
#AWS
suzuki.ryo

suzuki.ryo

facebook logohatena logotwitter logo
Clock Icon2020.04.05

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

AWSチームのすずきです。

2020年3月に実施した「Developers.IO」のリニューアル、インフラ側(バックエンド)で実施した変更について紹介させていただきます。

フロント側の変更については、諏訪の記事をご覧ください。

背景

2019年末、月間290万PV/90万UU のアクセス数を達成した「Developers.IO」。

2011年の開設当時よりCMSとしてWordPressを採用、記事などを管理するデータベースとしてAmazon Auroraを利用しています。

PHPが動作するEC2は、ElasticBeanstalkで管理、負荷に応じたオートスケール(スケールアウト)、配信性能の向上のため、CloudFront、Nginxを利用した最適化を実施しています。

WordPressは、Auroraのマスタノードのみを利用していたため、サイト規模の増加に比例してデータベースの負荷も上昇。 Aurora はスペックアップを行う事が可能ですが、データベース性能が不足した場合、応答遅延やサイトダウンに至る事がありました。

また、Auroraを最大ピークに合わせたスペックで稼働させた場合、 リザーブドインスタンスによる割引や、可用性の低下を許容した上でのSigleAZ利用であっても、月額のコストが課題となる状態でした。

Aurora1ノードの月額コスト試算

スペック オンデマンド 1年RI 3年RI
db.r5.large 256.2 142.0 95.4
db.r5.xlarge 512.4 284.0 190.8
db.r5.2xlarge 1024.8 567.9 381.5
db.r5.4xlarge 2049.6 1135.9 762.9

※東京リージョンの月額費用(USドル)。RIは全額前払を按分。

改善

記事の公開系を新しいアーキテクチャで刷新。データベースに高い負荷を発生させていた処理の改善を実施しました。

記事執筆に利用するCMSとしてのWordPressは、そのまま維持しています。

記事表示

WP REST API を利用して記事データを取得、サーバサイドレンダリング(SSR) を実施する Lambdaを導入しました。

SQL実行数

WordPressのPHPがDBを利用して実施していた記事HTMLの生成、オフロードされた事で、 1回の記事表示で発生するSQLの実行数は約1/5 (260→55)までに削減されました。

  • リニューアル前
$cat mysqlquery_dump.log | grep '13 Query' | while read line
do
 echo "${line}" | cut -c 1-80
done  | sed 's/.*Query\t//g'

SET NAMES utf8mb4
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_520_ci'
SELECT @@SESSION.sql_mode
SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION'
SELECT option_name, option_value FROM techblog_options WHERE autoload =
SELECT option_value FROM techblog_options WHERE option_name = 'disqus_a
SELECT option_value FROM techblog_options WHERE option_name = 'active_s
SELECT option_value FROM techblog_options WHERE option_name = 'ure_task
SELECT option_value FROM techblog_options WHERE option_name = 'uninstal
SELECT option_value FROM techblog_options WHERE option_name = 'wp-optim
SELECT option_value FROM techblog_options WHERE option_name = 'wp-optim
SELECT option_value FROM techblog_options WHERE option_name = 'wp-optim
SELECT option_value FROM techblog_options WHERE option_name = 'wp-optim
SELECT option_value FROM techblog_options WHERE option_name = 'classic-
SELECT option_value FROM techblog_options WHERE option_name = 'classic-
SELECT option_value FROM techblog_options WHERE option_name = '_transie
SELECT option_value FROM techblog_options WHERE option_name = '_transie
SELECT option_value FROM techblog_options WHERE option_name = 'ep_index
SELECT option_value FROM techblog_options WHERE option_name = '_transie
SELECT option_value FROM techblog_options WHERE option_name = '_transie
SELECT option_value FROM techblog_options WHERE option_name = 'can_comp
SELECT option_value FROM techblog_options WHERE option_name = 'ure_role
SHOW FULL COLUMNS FROM `techblog_options`
UPDATE `techblog_options` SET `option_value` = '1584701598.449275016784
SELECT option_value FROM techblog_options WHERE option_name = '_site_tr
SELECT option_value FROM techblog_options WHERE option_name = '_site_tr
SELECT option_value FROM techblog_options WHERE option_name = 'googleau
SELECT option_value FROM techblog_options WHERE option_name = 'wpcom_pu
UPDATE `techblog_options` SET `option_value` = '' WHERE `option_name` =
UPDATE `techblog_options` SET `option_value` = 'a:429:{s:27:"ranking/(\
SELECT ID, post_name, post_parent, post_type
SELECT   techblog_posts.* FROM techblog_posts  WHERE 1=1  AND techblog_
SELECT * FROM techblog_posts WHERE ID = 544694 LIMIT 1
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT term_id, meta_key, meta_value FROM techblog_termmeta WHERE term_
SELECT  t.*, tt.*, tr.object_id FROM techblog_terms AS t  INNER JOIN te
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT * FROM techblog_users WHERE ID = '6' LIMIT 1
SELECT user_id, meta_key, meta_value FROM techblog_usermeta WHERE user_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544756 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544773 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544750 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544761 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544764 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544763 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544757 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544771 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544772 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544758 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544753 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544759 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544755 AND source_
SELECT * FROM techblog_posts WHERE ID = 544776 LIMIT 1
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544776 AND source_
SELECT option_value FROM techblog_options WHERE option_name = 'disqus_r
SELECT p.ID FROM techblog_posts AS p  WHERE p.post_date < '2020-03-19 1
SELECT * FROM techblog_posts WHERE ID = 544787 LIMIT 1
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT term_id, meta_key, meta_value FROM techblog_termmeta WHERE term_
SELECT t.*, tt.* FROM techblog_terms AS t INNER JOIN techblog_term_taxo
SELECT p.ID FROM techblog_posts AS p  WHERE p.post_date > '2020-03-19 1
SELECT * FROM techblog_posts WHERE ID = 544092 LIMIT 1
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT term_id, meta_key, meta_value FROM techblog_termmeta WHERE term_
SELECT t.*, tt.* FROM techblog_terms AS t INNER JOIN techblog_term_taxo
SELECT tr.object_id FROM techblog_term_relationships AS tr INNER JOIN t
SELECT   techblog_posts.* FROM techblog_posts  WHERE 1=1  AND techblog_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT term_id, meta_key, meta_value FROM techblog_termmeta WHERE term_
SELECT t.*, tt.* FROM techblog_terms AS t INNER JOIN techblog_term_taxo
SELECT t.*, tt.* FROM techblog_terms AS t INNER JOIN techblog_term_taxo
SELECT t.*, tt.* FROM techblog_terms AS t INNER JOIN techblog_term_taxo
SELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM techblog_terms
SELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM techblog_terms
SHOW FULL COLUMNS FROM `techblog_terms`
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT *    FROM techblog_links  INNER JOIN techblog_term_relationships
SELECT meta_id FROM techblog_postmeta WHERE meta_key = '_social_status_
SHOW FULL COLUMNS FROM `techblog_postmeta`
UPDATE `techblog_postmeta` SET `meta_value` = '1584701598' WHERE `post_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT COUNT(*) FROM techblog_postmeta WHERE meta_key = '_twitter_count
SELECT meta_id FROM techblog_postmeta WHERE meta_key = '_twitter_count'
UPDATE `techblog_postmeta` SET `meta_value` = '27' WHERE `post_id` = 54
SELECT COUNT(*) FROM techblog_postmeta WHERE meta_key = '_facebook_coun
SELECT meta_id FROM techblog_postmeta WHERE meta_key = '_facebook_count
UPDATE `techblog_postmeta` SET `meta_value` = '65' WHERE `post_id` = 54
SELECT COUNT(*) FROM techblog_postmeta WHERE meta_key = '_view_count' A
SELECT meta_id FROM techblog_postmeta WHERE meta_key = '_view_count' AN
UPDATE `techblog_postmeta` SET `meta_value` = '32' WHERE `post_id` = 54
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT COUNT(*) FROM techblog_postmeta WHERE meta_key = '_marge_count'
SELECT meta_id FROM techblog_postmeta WHERE meta_key = '_marge_count' A
UPDATE `techblog_postmeta` SET `meta_value` = '110' WHERE `post_id` = 5
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT techblog_posts.* FROM techblog_posts WHERE ID IN (544756,544773,
SELECT SQL_CALC_FOUND_ROWS techblog_comments.comment_ID FROM techblog_c
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT *    FROM techblog_links  INNER JOIN techblog_term_relationships
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT *    FROM techblog_links  INNER JOIN techblog_term_relationships
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT *    FROM techblog_links  INNER JOIN techblog_term_relationships
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT SQL_CALC_FOUND_ROWS  techblog_posts.ID FROM techblog_posts  LEFT
SELECT FOUND_ROWS()
SELECT techblog_posts.* FROM techblog_posts WHERE ID IN (243685,61392,3
SELECT  t.*, tt.*, tr.object_id FROM techblog_terms AS t  INNER JOIN te
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_users WHERE ID = '62' LIMIT 1
SELECT user_id, meta_key, meta_value FROM techblog_usermeta WHERE user_
SELECT * FROM techblog_users WHERE ID = '138' LIMIT 1
SELECT user_id, meta_key, meta_value FROM techblog_usermeta WHERE user_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 307180 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 307178 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 307060 AND source_
SELECT SQL_CALC_FOUND_ROWS  techblog_posts.ID FROM techblog_posts  INNE
SELECT techblog_posts.* FROM techblog_posts WHERE ID IN (538934,537771,
SELECT  t.*, tt.*, tr.object_id FROM techblog_terms AS t  INNER JOIN te
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_users WHERE ID = '174' LIMIT 1
SELECT user_id, meta_key, meta_value FROM techblog_usermeta WHERE user_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 539015 AND source_
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT * FROM techblog_posts WHERE ID = 539015 LIMIT 1
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 537818 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 238539 AND source_
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT * FROM techblog_posts WHERE ID = 537819 LIMIT 1
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 537819 AND source_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 538607 AND source_
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT * FROM techblog_posts WHERE ID = 538607 LIMIT 1
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542550 AND source_
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT * FROM techblog_posts WHERE ID = 542550 LIMIT 1
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542520 AND source_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT * FROM techblog_posts WHERE ID = 542519 LIMIT 1
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542519 AND source_
SELECT SQL_CALC_FOUND_ROWS  techblog_posts.ID FROM techblog_posts  INNE
SELECT techblog_posts.* FROM techblog_posts WHERE ID IN (543378,543409,
SELECT  t.*, tt.*, tr.object_id FROM techblog_terms AS t  INNER JOIN te
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT  t.*, tt.*, tr.object_id FROM techblog_terms AS t  INNER JOIN te
SELECT * FROM techblog_users WHERE ID = '447' LIMIT 1
SELECT user_id, meta_key, meta_value FROM techblog_usermeta WHERE user_
SELECT * FROM techblog_posts WHERE ID = 498738 LIMIT 1
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 498738 AND source_
SELECT * FROM techblog_users WHERE ID = '275' LIMIT 1
SELECT user_id, meta_key, meta_value FROM techblog_usermeta WHERE user_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 543412 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 543540 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 543551 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 543547 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 543579 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 543581 AND source_
SELECT * FROM techblog_posts WHERE ID = 424624 LIMIT 1
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 424624 AND source_
SELECT   techblog_posts.* FROM techblog_posts  WHERE 1=1  AND techblog_
SELECT * FROM techblog_posts WHERE ID = 544030 LIMIT 1
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT   techblog_posts.* FROM techblog_posts  WHERE 1=1  AND techblog_
SELECT * FROM techblog_posts WHERE ID = 544031 LIMIT 1
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT techblog_posts.* FROM techblog_posts WHERE ID IN (543412,543540,
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 543998 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 543999 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544000 AND source_
SELECT * FROM techblog_posts WHERE ID = 436603 LIMIT 1
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 436603 AND source_
SELECT * FROM techblog_users WHERE ID = '208' LIMIT 1
SELECT user_id, meta_key, meta_value FROM techblog_usermeta WHERE user_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544156 AND source_
SELECT * FROM techblog_posts WHERE ID = 525172 LIMIT 1
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 525172 AND source_
SELECT SQL_CALC_FOUND_ROWS  techblog_posts.ID FROM techblog_posts  INNE
SELECT techblog_posts.* FROM techblog_posts WHERE ID IN (541782,538510,
SELECT  t.*, tt.*, tr.object_id FROM techblog_terms AS t  INNER JOIN te
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT  t.*, tt.*, tr.object_id FROM techblog_terms AS t  INNER JOIN te
SELECT * FROM techblog_users WHERE ID = '14' LIMIT 1
SELECT user_id, meta_key, meta_value FROM techblog_usermeta WHERE user_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 541790 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 541792 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 541834 AND source_
SELECT * FROM techblog_posts WHERE ID = 541783 LIMIT 1
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 541783 AND source_
SELECT * FROM techblog_users WHERE ID = '187' LIMIT 1
SELECT user_id, meta_key, meta_value FROM techblog_usermeta WHERE user_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 538513 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 538515 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 538614 AND source_
SELECT * FROM techblog_posts WHERE ID = 436597 LIMIT 1
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 436597 AND source_
SELECT * FROM techblog_users WHERE ID = '70' LIMIT 1
SELECT user_id, meta_key, meta_value FROM techblog_usermeta WHERE user_
SELECT * FROM techblog_posts WHERE ID = 379736 LIMIT 1
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 379736 AND source_
SELECT * FROM techblog_users WHERE ID = '83' LIMIT 1
SELECT user_id, meta_key, meta_value FROM techblog_usermeta WHERE user_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542972 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542970 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542971 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542973 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542975 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542976 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542977 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542978 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542979 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542980 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542981 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542982 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542984 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 542985 AND source_
SELECT * FROM techblog_posts WHERE ID = 467519 LIMIT 1
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 467519 AND source_
SELECT techblog_posts.* FROM techblog_posts WHERE ID IN (542972,542970,
SELECT *    FROM techblog_links  INNER JOIN techblog_term_relationships
SELECT t.*, tt.* FROM techblog_terms AS t INNER JOIN techblog_term_taxo
SELECT tr.object_id FROM techblog_term_relationships AS tr INNER JOIN t
SELECT   techblog_posts.* FROM techblog_posts  WHERE 1=1  AND techblog_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT   techblog_posts.* FROM techblog_posts  WHERE 1=1  AND techblog_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT term_id, meta_key, meta_value FROM techblog_termmeta WHERE term_
SELECT term_taxonomy_id, term_order from techblog_term_relationships wh
SELECT option_value FROM techblog_options WHERE option_name = '_transie
SELECT option_value FROM techblog_options WHERE option_name = '_transie
  • リニューアル後
SET NAMES utf8mb4
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_520_ci'
SELECT @@SESSION.sql_mode
SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION'
SELECT option_name, option_value FROM techblog_options WHERE autoload =
SELECT option_value FROM techblog_options WHERE option_name = 'disqus_a
SELECT option_value FROM techblog_options WHERE option_name = 'active_s
SELECT option_value FROM techblog_options WHERE option_name = 'ure_task
SELECT option_value FROM techblog_options WHERE option_name = 'uninstal
SELECT option_value FROM techblog_options WHERE option_name = 'wp-optim
SELECT option_value FROM techblog_options WHERE option_name = 'wp-optim
SELECT option_value FROM techblog_options WHERE option_name = 'wp-optim
SELECT option_value FROM techblog_options WHERE option_name = 'wp-optim
SELECT option_value FROM techblog_options WHERE option_name = 'classic-
SELECT option_value FROM techblog_options WHERE option_name = 'classic-
SELECT option_value FROM techblog_options WHERE option_name = '_transie
SELECT option_value FROM techblog_options WHERE option_name = '_transie
SELECT option_value FROM techblog_options WHERE option_name = 'ep_index
SELECT option_value FROM techblog_options WHERE option_name = '_transie
SELECT option_value FROM techblog_options WHERE option_name = '_transie
SELECT option_value FROM techblog_options WHERE option_name = 'ure_role
SELECT option_value FROM techblog_options WHERE option_name = '_site_tr
SELECT option_value FROM techblog_options WHERE option_name = '_site_tr
SELECT option_value FROM techblog_options WHERE option_name = 'googleau
SELECT option_value FROM techblog_options WHERE option_name = 'wpcom_pu
SHOW FULL COLUMNS FROM `techblog_options`
UPDATE `techblog_options` SET `option_value` = '' WHERE `option_name` =
UPDATE `techblog_options` SET `option_value` = 'a:429:{s:27:"ranking/(\
SELECT * FROM techblog_posts WHERE ID = 544694 LIMIT 1
SELECT * FROM techblog_users WHERE ID = '6' LIMIT 1
SELECT user_id, meta_key, meta_value FROM techblog_usermeta WHERE user_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT post_id, meta_key, meta_value FROM techblog_postmeta WHERE post_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544756 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544773 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544750 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544761 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544764 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544763 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544757 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544771 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544772 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544758 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544753 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544759 AND source_
SELECT * FROM techblog_as3cf_items WHERE source_id = 544755 AND source_
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT term_id, meta_key, meta_value FROM techblog_termmeta WHERE term_
SELECT techblog_posts.* FROM techblog_posts WHERE ID IN (544756,544773,
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT term_id, meta_key, meta_value FROM techblog_termmeta WHERE term_
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT  t.*, tt.* FROM techblog_terms AS t  INNER JOIN techblog_term_ta
SELECT   techblog_posts.ID FROM techblog_posts  WHERE 1=1  AND techblog

PHP処理時間

PHPの動作時間の短縮 (2.9 →0.1s)も確認できました。

  • リニューアル前
$ time curl http://localhost/blog/renewal-devio-2020/ > /dev/null
real    0m2.911s
  • リニューアル後(WP API)
$ time curl -v http://localhost/wp-json/wp/v2/posts/544694 > /dev/null
real    0m0.135s

データソースの使い分け

WordPressのデータベースを都度参照する方式だった執筆者、ランキングページを改めました。

  • リニューアル前

  • リニューアル後

記事、執筆者単位の集計結果を事前にJSON化してS3に保存。SSRを行うLambdaが非同期に参照する利用を実現しました。

   {
    "author_id": 6,
    "post_count": 642,
    "facebook_count": 12965,
    "hatena_count": 20656,
    "twitter_count": 16974,
    "display_name": "諏訪 悠紀",
    "user_nicename": "suwa-yuki",
    "sns_score": [
      {
        "post_date": "2011-07",
        "facebook_count": 5,
        "hatena_count": 5,
        "twitter_count": 5,
        "post_count": 2
      },
      {
        "post_date": "2011-08",
        "facebook_count": 19,
        "hatena_count": 59,
        "twitter_count": 23,
        "post_count": 3
      },

表示速度

執筆者ページの表示速度は、11秒から2秒台まで改善しました。

  • リニューアル前

  • リニューアル後

効果について

DBLoad(DB エンジンのアクティブセッション数)について、高負荷時(P99)の数値について約1/5となった事を確認できました。

パフォーマンスインサイトから Amazon CloudWatch に発行されるメトリクス

今後について

分類

負荷対策として表示を取り下げていた、カテゴリー、タグなどのメニューについて、2020年時点の記事内容に合わせて見直した上で、再公開を予定しています。

  • 2013年当時のDevelopers.IO

検索

既存の「DevelopersIO」サイト内検索は、WordPressのプラグイン「elasticpress」を利用して、Elasticseachを利用しています。

整備したタグ、カテゴリー情報をなどをクエリ対象としたUIや、表示系の改善を実施する事で、検索体験、精度の向上を図る予定です。

{"terms":{"properties":
  {
    "category":{"properties":{"name":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}}}},
    "post_tag":{"properties":{"name":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}}}}}
  }
}

ランキング

新しい執筆者ページ、以下の仕組みと連携して実現しています。

SNSシェア数集計システム、記事の他の要素についても扱う事が可能であるため、 今後はPV数などをランキング要素にするなどの拡張や、ストリーム処理を活用した速報ランキングなども提供出来ればと考えています。

Aurora serverless

データベースの負荷削減が実現できた事で、Aurora serverless を現実的なコストで利用できる可能性が高まりました。

オートスケールによるDB性能の自動調整や、Data API を利用したよりサーバレスなシステム連携の実現を試みる予定です。

また、別リージョンを利用した低コストなDR。

  • Aurora serverless、平時は停止状態で維持
  • S3を利用して、記事DBと画像データを同期 (リージョン間レプリケーション(CRR))

までを実現する事で、万一東京リージョンに大規模な障害が発生した場合でも、短時間で復旧サイトを再開できる見込みです。

まとめ

今回紹介させて頂いたリニューアル、約2ヶ月弱で実現できた成果になります。

  • 2/15 SSR実現Lambda、プロトタイプ完成
  • 2/27 新記事ページの先行公開 (6記事)
  • 3/6 実行環境(WordPress、PHP)の最新化、最適化
  • 3/22 記事ページ全面公開
  • 4/2 執筆者とランキングページのリリース

新旧記事ページの共存、切替や、データ連携、ログ管理などの仕組みについては追って紹介させて頂ければと思います。

Share this article

facebook logohatena logotwitter logo

関連記事

EC2 + RDS 環境へインストールした WordPress 上で AWS Secrets Manager Agent 経由で DB 接続情報のシークレットを取得してみた

EC2 + RDS 環境へインストールした WordPress 上で AWS Secrets Manager Agent 経由で DB 接続情報のシークレットを取得してみた

User avatar
いわさ
2024.07.15
英語日記をAIに添削してもらいWordPressに投稿する

英語日記をAIに添削してもらいWordPressに投稿する

User avatar
kato
2024.03.27
[アップデート] Amazon Lightsail の WordPress インスタンスでカスタムドメインや SSL 構成を簡単にセットアップする機能が使えるようになりました

[アップデート] Amazon Lightsail の WordPress インスタンスでカスタムドメインや SSL 構成を簡単にセットアップする機能が使えるようになりました

User avatar
いわさ
2024.01.06
Mountpoint for Amazon S3 を利用して、WordPress のメディアファイルをオフロードしてみた

Mountpoint for Amazon S3 を利用して、WordPress のメディアファイルをオフロードしてみた

User avatar
森田力
2023.12.26
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.